﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using log4net;
using SmallBusinessKit.DataAccess.Configurations;
using SmallBusinessKit.Models.Entities;

namespace SmallBusinessKit.DataAccess
{
    public class SmallBusinessContext : DbContext
    {
        #region Properties

        private readonly ILog   _log = LogManager.GetLogger(typeof(SmallBusinessContext));
        private readonly string _cs;

        public DbSet<Category>      Categories      { get; set; }
        public DbSet<Item>          Items           { get; set; }
        public DbSet<News>          News            { get; set; }
        public DbSet<Person>        People          { get; set; }
        public DbSet<Testimonial>   Testimonials    { get; set; }

        #endregion

        #region Methods

        public SmallBusinessContext()
        {
            //TODO: this can be injected at some point
            _cs = ConfigurationManager.ConnectionStrings["SmallBusinessContext"].ConnectionString;

            Database.SetInitializer<SmallBusinessContext>(null);
        }

        public SmallBusinessContext(IDatabaseInitializer<SmallBusinessContext> ctxInitializer)
        {
            _cs = ConfigurationManager.ConnectionStrings["SmallBusinessContext"].ConnectionString;  

            Database.SetInitializer(ctxInitializer);
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            try
            {
                modelBuilder.HasDefaultSchema("sbk");

                modelBuilder.Configurations.Add(new CategoryConfig());
                modelBuilder.Configurations.Add(new ItemConfig());
                modelBuilder.Configurations.Add(new NewsConfig());
                modelBuilder.Configurations.Add(new PersonConfig());
                modelBuilder.Configurations.Add(new TestimonialConfig());
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                throw;
            }

            base.OnModelCreating(modelBuilder);
        }
        
        // Reserved for future use (the CRUD ops & the Sprocs)

        public int Commit()
        {
            var x = 0;

            try
            {
                x = SaveChanges();
            }
            catch (DbEntityValidationException dbEx)
            {
                foreach (var validationError in dbEx.EntityValidationErrors.SelectMany(validationErrors => validationErrors.ValidationErrors))
                {
                    Trace.TraceInformation("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
                }
            }

            return x;
        }

        public DataTable ExecStoredProcUsingAdo(string storedProcName, Dictionary<string, object> parms = null)
        {
            var dt = new DataTable();

            try
            {
                using (var cn = new SqlConnection(_cs))
                {
                    var cmd = cn.CreateCommand();
                    cmd.CommandText    = storedProcName;
                    cmd.CommandType    = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600;

                    if (null != parms)
                    {
                        foreach (var kvp in parms)
                        {
                            _log.DebugFormat("Parm: {0} = {1}", kvp.Key, kvp.Value);

                            var parameter = new SqlParameter
                            {
                                ParameterName = $"@{kvp.Key}",
                                Direction = ParameterDirection.Input,
                                Value = kvp.Value,
                            };

                            cmd.Parameters.Add(parameter);
                        }
                    }

                    cn.Open();

                    using (var da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message, ex);
                throw;
            }

            return dt;
        }

        public IList<T>  ExecStoredProc<T>(string storedProcName, Dictionary<string, object> parms)
        {
            List<T> spResults;

            var idx = 0;
            var sqlParams = new object[parms.Count];
            var sql = new StringBuilder("EXEC " + storedProcName + " ");

            foreach (var kvp in parms)
            {
                _log.DebugFormat("Parm: {0} = {1}", kvp.Key, kvp.Value);

                sql.Append("@" + kvp.Key + ", ");

                var p = new SqlParameter
                {
                    ParameterName = "@" + kvp.Key,
                    Direction     = ParameterDirection.Input,
                    Value         = kvp.Value,
                };

                sqlParams[idx] = p;
                idx++;
            }

            try
            {
                spResults = Database.SqlQuery<T>(sql.ToString().Trim().TrimEnd(','), sqlParams).ToList();
            }
            catch (Exception ex)
            {
                _log.ErrorFormat(ex.Message);
                throw;
            }

            return spResults;
        }

        public IList<T>  ExecStoredProc<T>(string storedProcName)
        {
            IList<T> spResults;

            var sql = new StringBuilder("EXEC " + storedProcName + " ");

            try
            {
                spResults = Database.SqlQuery<T>(sql.ToString().Trim().TrimEnd(',')).ToList();
            }
            catch (Exception ex)
            {
                _log.ErrorFormat(ex.Message);
                throw;
            }

            return spResults;
        }
        
        /*
        // reserved for future use
        public void ExecuteRawSql(string sql)
        {
            Database.ExecuteSqlCommand(sql);
        }
        */
        #endregion
    }
}
